import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;

public class StockReportProcess extends HttpServlet
{
	public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException,IOException
	{
		doPost(request,response);
	}//doGet

	public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException,IOException
    {
		RequestDispatcher dispatcher = null;
		try
		{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/airtel","root","root");
			Statement stmt = con.createStatement();
			String ct = "",count = "";
			ResultSet rs = null;
			ResultSet rs1 = null;
			Collection simvector = new Vector();
			Collection easyvector = new Vector();
			Collection papervector = new Vector();
			String qry = "select * from cardtype where cardType like 'cd%'";
			System.out.println("sim sales"+qry);
			ResultSet cardrs = stmt.executeQuery(qry);
			while(cardrs.next())
			{
				String cardname = cardrs.getString(2);
				String qryy = "select count from simstock where cardType = "+cardrs.getString(1)+"";
				Statement stmt5 = con.createStatement();
				ResultSet countrs = stmt5.executeQuery(qryy);
				count = "";
				while(countrs.next())
				{
					count = countrs.getString(1);
				}
				if(!(count.equals(0) || count.equals("")))
				{
					simvector.add(cardname);
					simvector.add(count);
				}
			}		
			rs = stmt.executeQuery("select amount from easystock");
			if(rs.next())
			{
				System.out.println("___________"+rs.getString(1));
				easyvector.add(rs.getString(1));	
			}

			String qry4 = "select * from cardtype where cardType not like 'cd%'";
			System.out.println("sim sales"+qry4);
			Statement stmt9 = con.createStatement();
			ResultSet paperrs = stmt9.executeQuery(qry4);
			String countpaper = "";
			while(paperrs.next())
			{
				String papername = paperrs.getString(2);
				String qryy = "select count from paperstock where cardType = "+paperrs.getString(1)+"";
				System.out.println("~~~~~~~~~~"+qryy);
				Statement stmt7 = con.createStatement();
				ResultSet countrs1 = stmt7.executeQuery(qryy);
				count = "";
				while(countrs1.next())
				{
					count = countrs1.getString(1);
				}
				if(!(count.equals(0) || count.equals("")))
				{
					papervector.add(papername);
					papervector.add(count);
				}
			}				
				
			request.setAttribute("simstock",simvector);
			request.setAttribute("easystock",easyvector);
			request.setAttribute("paperstock",papervector);

			dispatcher = request.getRequestDispatcher("/jsp/StockReportForm.jsp");
			response.setHeader("Content-disposition","attachment; filename=Reports.html" );
			dispatcher.forward(request,response);
		}//try
		catch(Exception e)
		{
			e.printStackTrace();
		}//catch
	}//doPost
}//class